#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2023/6/2 11:07
# @Author  : TangZhongZheng
# @File    : main.py
# @Description :
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2023/6/2 11:00
# @Author  : TangZhongZheng
# @File    : main.py
# @Description :
import math
import os
import re
import warnings
import pymysql
import pandas as pd

from pandas import DataFrame
from pandas._libs.tslibs.nattype import NaTType
from config import *

pd.set_option('display.max_columns', None)
#显示所有行
pd.set_option('display.max_rows', None)
#设置value的显示长度为100，默认为50
pd.set_option('max_colwidth',100)

warnings.filterwarnings("ignore")

pd.options.mode.chained_assignment = None


def del_blank_row(data, aim):
    """
    给特定列去除空行
    :param data:
    :param aim:
    :return:
    """
    for index, row in data.iterrows():
        if isinstance(row[aim], float):
            if math.isnan(row[aim]):
                continue
        text = row[aim]
        # 假设 text 是一个包含空行的字符串
        lines = text.splitlines(True)  # 保留换行符
        # 过滤空行
        lines = [line for line in lines if line.strip()]
        # 重新拼接成字符串
        text = "".join(lines)
        data.loc[index, aim] = text


def deal_with_blank_row(data):
    """
    处理空行
    :param data:
    :return:
    """
    data = data[(data.index != 0)]
    del_blank_row(data, member_position)
    del_blank_row(data, member_otherinfo)
    del_blank_row(data, member_address)
    del_blank_row(data, member_education)
    return data

def deal_with__phone(data):
    """
    处理电话
    :param data:处理前的数据
    :return: data:处理后的数据
    """
    data[member_phone] = deal_with_symbol(data[member_phone])
    data[member_phone]=data[member_phone].str.replace(" ","")
    data[member_phone]=data[member_phone].str.replace("-","")
    return data

def deal_with__address(data):
    data[member_address] = deal_with_symbol(data[member_address])
    return data;

def deal_with__position(data):
    data[member_position] = deal_with_symbol(data[member_position])
    return data;

def deal_with__education(data):
    data[member_education] = deal_with_symbol(data[member_education])
    return data;

def deal_with_symbol(data):
    data = data.str.replace("&", "")
    data = data.str.replace("$", "")
    data= data.str.replace(" @@", ",")
    data = data.str.rstrip()
    return data

def deal_with_gender(data):
    """
    处理性别
    :param data:处理前的数据
    :return data:处理后的数据
    """
    data[member_gender][data[member_gender] == '男'] = '1'
    data[member_gender][data[member_gender] == 'male'] = '1'
    data[member_gender][data[member_gender] == '女'] = '0'
    data[member_gender][data[member_gender] == 'female'] = '0'
    return data


def deal_with_none(data):
    """
    处理空数据
    :param data:
    :return:
    """
    data.fillna(value='', inplace=True)
    return data


def deal_with_date(data):
    """
    处理日期
    :param data:
    :return:
    """
    data[member_birth] = pd.to_datetime(data[member_birth], errors='ignore')
    return data


def deal_with(data: DataFrame, i):
    """
    处理数据的流程，如果需要则可以加入流程，按照deal_with_XXX来命名
    :param data:
    :param i:
    :return:
    """
    data = deal_with_blank_row(data)
    data = deal_with_none(data)
    data = deal_with_gender(data)
    data = deal_with_date(data)
    data = deal_with__phone(data)
    deal_with__address(data)
    deal_with__position(data)
    data.to_excel(out + "\\" + str(i) + ".xlsx")
    return data


def isEmpty(data):
    if data == '' or data is None or type(data) == NaTType:
        return None
    return data


# def insert_data(data: pd.DataFrame, file):
#     with open(logs + "\\" + file + ".log", mode='w', encoding='utf-8') as f:
#         for i in range(len(data)):
#             sql = "INSERT INTO member_info(membername_CN, membername_EN, membername_ALIAS, " \
#                   "member_identity, member_status, member_gender," \
#                   "member_birth, member_height, member_weight, " \
#                   "member_marriage, member_country, member_party," \
#                   "member_religion, member_position, member_education, " \
#                   "member_phone, member_mail, member_address," \
#                   "member_linkedin, member_facebook, member_twitter, " \
#                   "member_instagram, member_youtube,member_telegram, " \
#                   "member_otherinfo) " \
#                   "VALUES" \
#                   "(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
#             try:
#                 cursor.execute(sql, (isEmpty(data[membername_CN][i]), isEmpty(data[membername_EN][i]),
#                                      isEmpty(data[membername_ALIAS][i]), isEmpty(data[member_identity][i]),
#                                      isEmpty(data[member_status][i]),
#                                      isEmpty(data[member_gender][i]), isEmpty(data[member_birth][i]),
#                                      isEmpty(data[member_height][i]),
#                                      isEmpty(data[member_weight][i]), isEmpty(data[member_marriage][i]),
#                                      isEmpty(data[member_country][i]),
#                                      isEmpty(data[member_party][i]), isEmpty(data[member_religion][i]),
#                                      isEmpty(data[member_position][i]),
#                                      isEmpty(data[member_education][i]), isEmpty(data[member_phone][i]),
#                                      isEmpty(data[member_mail][i]),
#                                      isEmpty(data[member_address][i]), isEmpty(data[member_linkedin][i]),
#                                      isEmpty(data[member_facebook][i]),
#                                      isEmpty(data[member_twitter][i]), isEmpty(data[member_instagram][i]),
#                                      isEmpty(data[member_youtube][i]),
#                                      isEmpty(data[member_telegram][i]), isEmpty(data[member_otherinfo][i])))
#                 db.commit()
#             except Exception as e:
#                 info = str(i) + str(e)
#                 f.write(info + "\n")
#                 db.rollback()


def createDir():
    if not os.path.isdir(logs):
        # 创建目录
        os.makedirs(logs)
    if not os.path.isdir(out):
        # 创建目录
        os.makedirs(out)
    if not os.path.isdir(file_path_name):
        # 创建目录
        os.makedirs(file_path_name)


if __name__ == '__main__':
    # db = pymysql.connect(host=host, user=user, password=password, database=table_name)
    # cursor = db.cursor()
    i = 0

    for subdir, dirs, files in os.walk(file_path_name):
        for file in files:
            path = os.path.join(subdir, file)
            data = pd.read_excel(io=path)
            data = deal_with(data, i)
            # insert_data(data, file)
            i += 1
            print(path, 'done')
    # db.close()
